"""
利用Python进行数据分析

数据规整化：清理、转换、合并、重塑
"""
import numpy as np
from pandas import Series, DataFrame
import pandas as pd


def test_01():
    """
    数据库风格的DataFrame合并
    :return:
    """
    # df1 = DataFrame({'key': ['b', 'b', 'a', 'c', 'a', 'a', 'b'], 'data1': range(7)})
    # print(df1)
    # df2 = DataFrame({'key': ['a', 'b', 'd'], 'data2': range(3)})
    # print(df2)
    # # df3 = pd.merge(df1, df2)
    # df3 = pd.merge(df1, df2, on='key')
    # print(df3)

    # df3 = DataFrame({'lkey': ['b', 'b', 'a', 'c', 'a', 'a', 'b'], 'data1': range(7)})
    # print(df3)
    # df4 = DataFrame({'rkey': ['a', 'b', 'd'], 'data2': range(3)})
    # print(df4)
    # df5 = pd.merge(df3, df4, left_on='lkey', right_on='rkey')
    # print(df5)
    #
    # df6 = pd.merge(df3, df4, left_on='lkey', right_on='rkey', how='outer')
    # print(df6)
    #
    # df7 = pd.merge(df3, df4, left_on='lkey', right_on='rkey', how='left')
    # print(df7)
    #
    # df8 = pd.merge(df3, df4, left_on='lkey', right_on='rkey', how='right')
    # print(df8)

    # # 多对多的合并操作
    # df1 = DataFrame({'key': ['b', 'b', 'a', 'c', 'a', 'b'], 'data1': range(6)})
    # df2 = DataFrame({'key': ['a', 'b', 'a', 'b', 'd'], 'data1': range(5)})
    # df3 = pd.merge(df1, df2, on='key', how='left')
    # print(df3)
    # df4 = pd.merge(df1, df2, on='key', how='inner')
    # print(df4)

    # 根据多个键进行合并
    left = DataFrame({'key1': ['foo', 'foo', 'bar'], 'key2': ['one', 'two', 'one'], 'lval': [1, 2, 3]})
    print(left)
    right = DataFrame(
        {'key1': ['foo', 'foo', 'bar', 'bar'], 'key2': ['one', 'one', 'one', 'two'], 'lval': [4, 5, 6, 7]})
    print(right)
    # result = pd.merge(left, right, on=['key1', 'key2'], how='outer')
    result = pd.merge(left, right, on='key1')
    print(result)
    result = pd.merge(left, right, on='key1', suffixes=('_left', '_right'))
    print(result)


def test_02():
    """
    索引上的合并
    :return:
    """
    # left1 = DataFrame({'key': ['a', 'b', 'a', 'a', 'b', 'c']})
    # print(left1)
    # right1 = DataFrame({'group_val': [3.5, 7]}, index=['a', 'b'])
    # print(right1)
    # # df = pd.merge(left1, right1, left_on='key', right_index=True)
    # df = pd.merge(left1, right1, left_on='key', right_index=True, how='outer')
    # print(df)

    # # 层次化索引
    # lefth = DataFrame({'key1': ['Ohio', 'Ohio', 'Ohio', 'Nevada', 'Nevada'],
    #                    'key2': [2000, 2001, 2002, 2001, 2002],
    #                    'data': np.arange(5.)})
    # print(lefth)
    # righth = DataFrame(np.arange(12).reshape((6, 2)),
    #                    index=[['Nevada', 'Nevada', 'Ohio', 'Ohio', 'Ohio', 'Ohio'],
    #                           [2001, 2000, 2000, 2000, 2001, 2002]],
    #                    columns=['event1', 'event2'])
    # print(righth)
    # # df = pd.merge(lefth, righth, left_on=['key1', 'key2'], right_index=True)
    # df = pd.merge(lefth, righth, left_on=['key1', 'key2'], right_index=True, how='outer')
    # print(df)

    # 同时使用合并双方的索引
    left2 = DataFrame([[1., 2.], [3., 4.], [5., 6.]], index=['a', 'c', 'e'],
                      columns=['Ohio', 'Nevada'])
    print(left2)
    right2 = DataFrame([[7., 8.], [9., 10.], [11., 12.], [13., 14.]],
                       index=['b', 'c', 'd', 'e'],
                       columns=['Missouri', 'Alabama'])
    print(right2)
    # df = pd.merge(left2, right2, how='outer', left_index=True, right_index=True)
    # df = left2.join(right2, how='outer')
    # print(df)

    another = DataFrame([[7., 8.], [9., 10.], [11., 12.], [16., 17.]],
                        index=['a', 'c', 'e', 'f'],
                        columns=['New York', 'Oregon'])
    print(another)
    # df = left2.join([right2, another])
    df = left2.join([right2, another], how='outer')
    print(df)


def test_03():
    """
    轴向连接
    :return:
    """
    # arr = np.arange(12).reshape((3, 4))
    # print(arr)
    #
    # arr2 = np.concatenate([arr, arr], axis=1)
    # print(arr2)

    # s1 = Series([0, 1], index=['a', 'b'])
    # print(s1)
    # s2 = Series([2, 3, 4], index=['c', 'd', 'e'])
    # s3 = Series([5, 5], index=['f', 'g'])
    # s = pd.concat([s1, s2, s3])
    # print(s)
    # df = pd.concat([s1, s2, s3], axis=1)
    # print(df)

    # s4 = pd.concat([s1 * 5, s3])
    # print(s4)
    # df = pd.concat([s1, s4], axis=1)
    # df = pd.concat([s1, s4], axis=1, join='inner')
    # df = pd.concat([s1, s4], axis=1, join_axes=[['a', 'c', 'b', 'e']])
    # print(df)

    # result = pd.concat([s1, s2, s3], keys=['one', 'two', 'three'])
    # print(result)
    # print(result.unstack())

    # result = pd.concat([s1, s2, s3], axis=1, keys=['one', 'two', 'three'])
    # print(result)

    # df1 = DataFrame(np.arange(6).reshape((3, 2)), index=['a', 'b', 'c'], columns=['one', 'two'])
    # print(df1)
    # df2 = DataFrame(5 + np.arange(4).reshape((2, 2)), index=['a', 'c'], columns=['three', 'four'])
    # print(df2)
    # # result = pd.concat([df1, df2], axis=1, keys=['level1', 'level2'], sort=False)
    # # result = pd.concat({'level1': df1, 'level2': df2}, axis=1, sort=False)
    # result = pd.concat([df1, df2], axis=1, keys=['level1', 'level2'], names=['upper', 'lower'], sort=False)
    # print(result)

    df1 = DataFrame(np.random.randn(3, 4), columns=['a', 'b', 'c', 'd'])
    print(df1)
    df2 = DataFrame(np.random.randn(2, 3), columns=['b', 'd', 'a'])
    print(df2)
    result = pd.concat([df1, df2], ignore_index=True)
    print(result)


def test_04():
    """
    合并重叠数据
    :return:
    """
    # a = Series([np.nan, 2.5, np.nan, 3.5, 4.5, np.nan],
    #            index=['f', 'e', 'd', 'c', 'b', 'a'])
    # print(a)
    # b = Series(np.arange(len(a), dtype=np.float),
    #            index=['f', 'e', 'd', 'c', 'b', 'a'])
    # b[-1] = np.nan
    # print(b)
    # # were = np.where(pd.isnull(a), b, a)
    # # print(were)
    #
    # print(b[:-2])
    # print(a[2:])
    # cf = b[:-2].combine_first(a[2:])
    # print(cf)

    df1 = DataFrame({'a': [1., np.nan, 5., np.nan],
                     'b': [np.nan, 2., np.nan, 6.],
                     'c': range(2, 18, 4)})
    print(df1)
    df2 = DataFrame({'a': [5., 4., np.nan, 3., 7.],
                     'b': [np.nan, 3., 4., 6., 8.]})
    print(df2)
    cf = df1.combine_first(df2)
    print(cf)


def main():
    # test_01()
    # test_02()
    # test_03()
    test_04()


if __name__ == '__main__':
    main()
